using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace SS.core
{
	internal class customers
	{
		private Func objFunc = new Func();
		public customers()
		{
			Program.strConnectionString = this.objFunc.strConnectionString;
		}
		public void loadToDataGridView(DataGridView dgv, BindingNavigator bdn)
		{
			dgv.AutoGenerateColumns = false;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string selectCommandText = "SELECT * FROM [TabPartner] WHERE [IsCustomer] = 1 ORDER BY [Name]";
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommandText, sqlConnection);
			DataSet dataSet = new DataSet();
			sqlDataAdapter.Fill(dataSet, "TabPartner");
			BindingSource bindingSource = new BindingSource();
			bindingSource.DataSource = dataSet.Tables["TabPartner"];
			bdn.BindingSource = bindingSource;
			dgv.DataSource = bindingSource;
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public string getRetailID()
		{
			string result = "";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT TOP 1 [CusID] FROM [TabPartner] WHERE [IsCustomer] = 1 ORDER BY [Name]";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				result = sqlDataReader["CusID"].ToString();
			}
			sqlDataReader.Close();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public void loadToListView1(ListView lv)
		{
			lv.Items.Clear();
			int num = 0;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT Name,Address,Phone,CusID FROM [TabPartner] WHERE [IsCustomer] = 1 ORDER BY [Name]";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				lv.Items.Add(sqlDataReader["Name"].ToString().Trim());
				lv.Items[num].SubItems.Add(sqlDataReader["Address"].ToString() + "  ");
				lv.Items[num].SubItems.Add(sqlDataReader["Phone"].ToString().Replace("\"", "") + " ");
				lv.Items[num].SubItems.Add(sqlDataReader["CusID"].ToString().Trim());
				num++;
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void loadToListView(ListView lv, string searchFor)
		{
			lv.Items.Clear();
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT * FROM  [TabPartner] WHERE [IsCustomer] = 1 AND [Name] LIKE N'%" + searchFor + "%' ORDER BY [Name]";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			int num = 0;
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					lv.Items.Add((num + 1).ToString());
					lv.Items[num].UseItemStyleForSubItems = false;
					lv.Items[num].SubItems.Add(sqlDataReader["CusID"].ToString());
					lv.Items[num].SubItems.Add(sqlDataReader["Name"].ToString());
					lv.Items[num].SubItems.Add(sqlDataReader["Address"].ToString());
					lv.Items[num].SubItems.Add(sqlDataReader["Phone"].ToString().Replace("\"", ""));
					num++;
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void loadToListViewForSearch(ListView lv, string searchForName, string searchForPhone, string searchForVaseID)
		{
			lv.Items.Clear();
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string text = "SELECT Name,Address,Phone,CusID FROM  [TabPartner] WHERE [IsCustomer] = 1 ";
			if (searchForName != "")
			{
				text = text + "  AND  UPPER([Name]) LIKE N'%" + searchForName.ToUpper() + "%' ";
			}
			if (searchForPhone != "")
			{
				text = text + "  AND  UPPER([Phone]) LIKE N'%" + searchForPhone.ToUpper() + "%' ";
			}
			if (searchForVaseID != "")
			{
				text = text + "  AND  UPPER([VaseID]) LIKE N'%" + searchForVaseID.ToUpper() + "%' ";
			}
			text += "  ORDER BY [Name]";
			SqlCommand sqlCommand = new SqlCommand(text, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			int num = 0;
			while (sqlDataReader.Read())
			{
				lv.Items.Add(sqlDataReader["Name"].ToString().Trim());
				lv.Items[num].SubItems.Add(sqlDataReader["Address"].ToString() + " ");
				lv.Items[num].SubItems.Add(sqlDataReader["Phone"].ToString().Replace("\"", "") + " ");
				lv.Items[num].SubItems.Add(sqlDataReader["CusID"].ToString().Trim());
				num++;
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void loadToListViewByPhone(ListView lv, string phoneSearch)
		{
			lv.Items.Clear();
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT * FROM  [TabPartner] WHERE [IsCustomer] = 1 AND UPPER([Phone]) LIKE N'%" + phoneSearch + "%' ORDER BY [Name]";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			int num = 0;
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					lv.Items.Add((num + 1).ToString());
					lv.Items[num].UseItemStyleForSubItems = false;
					lv.Items[num].SubItems.Add(sqlDataReader["CusID"].ToString());
					lv.Items[num].SubItems.Add(sqlDataReader["Name"].ToString());
					lv.Items[num].SubItems.Add(sqlDataReader["Address"].ToString());
					lv.Items[num].SubItems.Add(sqlDataReader["Phone"].ToString().Replace("\"", ""));
					num++;
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void getInfo(string cID, TextBox txtID, ComboBox cbbCusGroup, TextBox txtName, TextBox txtAddress, TextBox txtPhone, TextBox txtFax, TextBox txtEmail, TextBox txtTax, ComboBox cbbCustomerType, CheckBox ckb)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT * FROM [TabPartner] WHERE [CusID] = '" + cID.ToString() + "'";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					txtID.Text = sqlDataReader["CusID"].ToString();
					txtName.Text = sqlDataReader["Name"].ToString();
					txtAddress.Text = sqlDataReader["Address"].ToString();
					txtPhone.Text = sqlDataReader["Phone"].ToString();
					txtFax.Text = sqlDataReader["Fax"].ToString();
					txtEmail.Text = sqlDataReader["Email"].ToString();
					txtTax.Text = sqlDataReader["Tax"].ToString();
					cbbCusGroup.SelectedValue = sqlDataReader["CusGroup"].ToString();
					if (sqlDataReader["IsProvider"].ToString() == "1")
					{
						ckb.Checked = true;
					}
					else
					{
						ckb.Checked = false;
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void getInfo(string cID, TextBox txtID, TextBox txtName, TextBox txtAddress, TextBox txtPhone, TextBox txtFax, TextBox txtEmail, TextBox txtTax, TextBox txtGCustomer_Notes, CheckBox ckb, TextBox txtProID)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT * FROM [TabPartner] WHERE [CusID] = '" + cID.ToString() + "'";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					txtID.Text = sqlDataReader["CusID"].ToString();
					txtName.Text = sqlDataReader["Name"].ToString();
					txtAddress.Text = sqlDataReader["Address"].ToString();
					txtPhone.Text = sqlDataReader["Phone"].ToString();
					txtFax.Text = sqlDataReader["Fax"].ToString();
					txtEmail.Text = sqlDataReader["Email"].ToString();
					txtTax.Text = sqlDataReader["Tax"].ToString();
					txtGCustomer_Notes.Text = sqlDataReader["CusNote"].ToString();
					if (sqlDataReader["ProID"].ToString() != "")
					{
						ckb.Checked = true;
						txtProID.Text = sqlDataReader["ProID"].ToString();
					}
					else
					{
						ckb.Checked = false;
						txtProID.Text = "";
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void getInfoWithVaseID(string cID, TextBox txtID, TextBox txtName, TextBox txtAddress, TextBox txtPhone, TextBox txtFax, TextBox txtEmail, TextBox txtTax, TextBox txtGCustomer_Notes, TextBox txtProID, TextBox txtVaseID)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT * FROM [TabPartner] WHERE [CusID] = '" + cID.ToString() + "'  AND CusID<>'' ";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					txtID.Text = sqlDataReader["CusID"].ToString();
					txtName.Text = sqlDataReader["Name"].ToString();
					txtAddress.Text = sqlDataReader["Address"].ToString();
					txtPhone.Text = sqlDataReader["Phone"].ToString();
					txtFax.Text = sqlDataReader["Fax"].ToString();
					txtEmail.Text = sqlDataReader["Email"].ToString();
					txtTax.Text = sqlDataReader["Tax"].ToString();
					txtVaseID.Text = sqlDataReader["VaseID"].ToString();
					txtGCustomer_Notes.Text = sqlDataReader["CusNote"].ToString();
					if (sqlDataReader["ProID"].ToString() != "")
					{
						txtProID.Text = sqlDataReader["ProID"].ToString();
					}
					else
					{
						txtProID.Text = "";
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void getInfo(string cID, TextBox txtID, TextBox txtName, TextBox txtAddress, TextBox txtPhone, TextBox txtFax, TextBox txtEmail, TextBox txtTax, TextBox txtGCustomer_Notes, ComboBox cbbCustomerType, CheckBox ckb, TextBox txtProID)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT * FROM [TabPartner] WHERE [CusID] = '" + cID.ToString() + "'  AND CusID<>'' ";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			if (sqlDataReader.HasRows)
			{
				while (sqlDataReader.Read())
				{
					txtID.Text = sqlDataReader["CusID"].ToString();
					txtName.Text = sqlDataReader["Name"].ToString();
					txtAddress.Text = sqlDataReader["Address"].ToString();
					txtPhone.Text = sqlDataReader["Phone"].ToString();
					txtFax.Text = sqlDataReader["Fax"].ToString();
					txtEmail.Text = sqlDataReader["Email"].ToString();
					txtTax.Text = sqlDataReader["Tax"].ToString();
					txtGCustomer_Notes.Text = sqlDataReader["CusNote"].ToString();
					cbbCustomerType.SelectedIndex = int.Parse(sqlDataReader["CusType"].ToString());
					if (sqlDataReader["ProID"].ToString() != "")
					{
						ckb.Checked = true;
						txtProID.Text = sqlDataReader["ProID"].ToString();
					}
					else
					{
						ckb.Checked = false;
						txtProID.Text = "";
					}
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public void getInfo(string cID, TextBox txtName, Label txtAddress, Label txtPhone)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT Name,Address,Phone FROM [TabPartner] WHERE [CusID] = '" + cID.ToString() + "' AND CusID<>'' ";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				txtName.Text = sqlDataReader["Name"].ToString();
				txtAddress.Text = sqlDataReader["Address"].ToString();
				txtPhone.Text = sqlDataReader["Phone"].ToString();
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public string getInfo(string phoneNum, Label lblName, Label lblAddress, Label lblPhone, Label lblFax, Label lblTax)
		{
			string result = "";
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT * FROM [TabPartner] WHERE CHarIndex('\"" + phoneNum + "\"',Phone) > 0  AND CusID<>'' ";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				result = sqlDataReader["CusID"].ToString();
				lblName.Text = sqlDataReader["Name"].ToString().ToUpper();
				lblAddress.Text = sqlDataReader["Address"].ToString().ToUpper();
				lblPhone.Text = sqlDataReader["Phone"].ToString().Replace("\"", "").ToUpper();
				lblFax.Text = sqlDataReader["Fax"].ToString().ToUpper();
				lblTax.Text = sqlDataReader["Tax"].ToString().ToUpper();
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
		public void getInfo(string cusID, TextBox txtAddress, TextBox txtPhone)
		{
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT Address,Phone FROM [TabPartner] WHERE [CusID] = '" + cusID + "'  AND CusID<>'' ";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				txtAddress.Text = sqlDataReader["Address"].ToString();
				txtPhone.Text = sqlDataReader["Phone"].ToString().Replace("\"", "").ToUpper();
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
		}
		public int del(string id)
		{
			int result;
			try
			{
				SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
				sqlConnection.Open();
				SqlCommand sqlCommand = new SqlCommand("DelPartner", sqlConnection);
				sqlCommand.CommandType = CommandType.StoredProcedure;
				sqlCommand.Parameters.Add("ID", SqlDbType.VarChar, 50);
				sqlCommand.Parameters.Add("IsProvider", SqlDbType.SmallInt);
				sqlCommand.Parameters["ID"].Value = id;
				sqlCommand.Parameters["IsProvider"].Value = 0;
				sqlCommand.ExecuteNonQuery();
				sqlConnection.Close();
				sqlConnection.Dispose();
				result = 0;
			}
			catch
			{
				result = 1;
			}
			return result;
		}
		public bool existsNameCustomerFree(string Name)
		{
			bool result = false;
			SqlConnection sqlConnection = new SqlConnection(Program.strConnectionString);
			sqlConnection.Open();
			string cmdText = "SELECT [Name] FROM [TabCustomerFree] WHERE [Name] = N'" + Name + "'";
			SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
			SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
			while (sqlDataReader.Read())
			{
				result = true;
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
	}
}
